package cn.gzmli.dtt.common.service;

import cn.gzmli.dtt.common.config.cache.Config;
import cn.gzmli.dtt.common.dao.UserDao;
import cn.gzmli.dtt.common.entity.PageInfo;
import cn.gzmli.dtt.common.entity.Result;
import cn.gzmli.dtt.common.entity.Role;
import cn.gzmli.dtt.common.entity.User;
import cn.gzmli.dtt.common.entity.vo.ProjectVO;
import cn.gzmli.dtt.common.entity.vo.UserVO;
import cn.gzmli.dtt.common.utils.BaseService;
import cn.gzmli.dtt.common.utils.BaseUtil;
import cn.gzmli.dtt.common.utils.Constant;
import cn.gzmli.dtt.sbzyc.exception.SystemException;
import com.xiaoleilu.hutool.util.StrUtil;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.cache.annotation.CacheEvict;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.stereotype.Service;
import org.springframework.transaction.annotation.Transactional;

import javax.servlet.http.HttpServletRequest;
import java.util.*;

/**
 * @Descript
 * @Author xsx
 * @Date 2017/12/25
 */
@Service
@Transactional
public class UserService extends BaseService<User> {

    @Autowired
    private UserDao userDao;

    @Autowired
    private JdbcTemplate jdbcTemplate;

    public List<Map<String, Object>> getByCondition(HttpServletRequest request, PageInfo pageInfo) throws Exception {
        Map<String, Object> condition = this.getCondition(request);
        StringBuilder sql = new StringBuilder();
        sql.append("SELECT * FROM(")
                .append("SELECT u.*,")
                .append("dep.name AS departmentName,")
                .append("GROUP_CONCAT(DISTINCT d.id) AS departmentIds,")
                .append("GROUP_CONCAT(DISTINCT d.name) AS departmentNames,")
                .append("GROUP_CONCAT(DISTINCT r.id) AS roleIDS,")
                .append("GROUP_CONCAT(DISTINCT r.name) AS roleNames ")
                .append("FROM user u ")
                .append("LEFT JOIN  department dep ON dep.id=u.departmentId ")
                .append("LEFT JOIN  user_org uo ON uo.userId=u.id ")
                .append("LEFT JOIN  department d ON d.id=uo.orgId ")
                .append("LEFT JOIN user_role ur ON ur.userId=u.id ")
                .append("LEFT JOIN role r ON r.id=ur.roleId ")
                .append("GROUP BY u.id)tab ")
                .append("WHERE 1=1 ");
        if (StrUtil.isNotBlank(request.getParameter("departmentIDS"))) {
            String departmentIdsStr=request.getParameter("departmentIDS");
            String[] departmentIds=null;
            if(departmentIdsStr.indexOf(',')!=-1){
                departmentIds=departmentIdsStr.split(",");
            }else{
                departmentIds=new String[]{departmentIdsStr};
            }
            for(int i=0;i< departmentIds.length;i++){
                if(i==0) {
                    sql.append(" AND (FIND_IN_SET('" + departmentIds[i] + "',departmentIds) ");
                }else if(i+1<departmentIds.length){
                    sql.append(" OR FIND_IN_SET('" + departmentIds[i] + "',departmentIds) ");
                }
                if(i+1==departmentIds.length){
                    sql.append(") ");
                }
            }

        }
        return BaseUtil.getData(sql, condition, pageInfo, userDao, "id", false);
    }

    @Override
    public User save(User user) throws Exception {
        Map<String, String> condition = new HashMap<>();
        condition.put("userName", "=" + StrUtil.wrap(user.getUserName(), "'", "'"));
        condition.put("status", "=" + StrUtil.wrap(Constant.ENABLE+"", "'", "'"));
        User u = this.getByCondition(condition);
        if (u == null) {
            user.setId((Integer) userDao.insert(user));
            return user;
        }
        return null;
    }

    @Override
    public void update(User user) throws Exception {
        Map<String, String> condition = new HashMap<>();
        condition.put("id", "=" + user.getId());
        User u = this.getByCondition(condition);
        if (u != null) {
            user.setRoles(this.get(user.getId()).getRoles());
            userDao.update(user);
        } else {
            throw new SystemException("该名称已存在");
        }
    }

    public int updateStatus(User user) throws Exception {
        final String sql = "UPDATE user SET status=? WHERE id=?";
        return userDao.getJdbcTemplate().update(sql, user.getStatus(), user.getId());
    }

    public int updateHeaderPath(Integer userId, String headerpath) throws Exception {
        final String sql = "UPDATE user SET headerpath=? WHERE id=?";
        return  userDao.getJdbcTemplate().update(sql, headerpath, userId);
    }

    public User updateRoles(HttpServletRequest request, Integer id, String roleIDS) throws Exception {
        User user = this.get(id);
        Set<Role> roles = null;
        if (StrUtil.isNotBlank(roleIDS)) {
            roles = new HashSet<>();
            for (String roleID : roleIDS.split(",")) {
                Role role = new Role();
                role.setId(Integer.valueOf(roleID));
                roles.add(role);
            }
        }
        user.setRoles(roles);
        userDao.update(user);

        Config.updateUser(request, user);
        return user;
    }

    @Transactional(rollbackFor=Exception.class)
    public void addOrUpdateOrgRole(Integer userId, String orgIDs, String roleIDs) throws Exception {

        //先移除,再添加
        String deleteOrgSql = "DELETE FROM user_org WHERE userId= "+userId;
        userDao.getJdbcTemplate().execute(deleteOrgSql);
        String deleteRoleSql = "DELETE FROM user_role WHERE userId= "+userId;
        userDao.getJdbcTemplate().execute(deleteRoleSql);

        StringBuilder insertOrgSql = new StringBuilder("INSERT INTO user_org (userId, orgId) ");
        StringBuilder insertRoleSql = new StringBuilder("INSERT INTO user_role (userId, roleId) ");
        List<Object> orgArgs = new ArrayList<Object>();
        List<Object> roleArgs = new ArrayList<Object>();
        if (StrUtil.isNotBlank(orgIDs)){
            for (String orgId : orgIDs.split(",")){
                orgArgs.add(userId);
                orgArgs.add(orgId);
                insertOrgSql.append(" SELECT ?,? FROM DUAL UNION");
            }
            String executeOrgSql = insertOrgSql.substring(0, insertOrgSql.lastIndexOf("UNION"));
            userDao.getJdbcTemplate().update(executeOrgSql.toString(), orgArgs.toArray());
        }
        if (StrUtil.isNotBlank(roleIDs)){
            for (String roleId : roleIDs.split(",")){
                roleArgs.add(userId);
                roleArgs.add(roleId);
                insertRoleSql.append(" SELECT ?,? FROM DUAL UNION");
            }
            String executeRoleSql = insertRoleSql.substring(0, insertRoleSql.lastIndexOf("UNION"));
            userDao.getJdbcTemplate().update(executeRoleSql.toString(), roleArgs.toArray());
        }
    }

    public List<Map<String, Object>> getTree(HttpServletRequest request) throws Exception {
        Map<String, Object> condition = this.getCondition(request);
        StringBuilder sql = new StringBuilder();
        sql.append("SELECT CONCAT(id, 'dttUser') AS id,")
                .append("id AS realID,")
                .append("name AS name,")
                .append("CONCAT(departmentId, 'dttD') AS pId,")
                .append("'User' AS nodeType ")
                .append("FROM user ")
                .append("WHERE status=1 ");
        return BaseUtil.getData(sql, condition, userDao);
    }

    public List<Map<String, Object>> findserByDepartmentId(Integer departmentId) throws Exception {

        StringBuffer sql = new StringBuffer()
                .append(" SELECT ")
                .append(" id, ")
                .append(" name ")
                .append(" FROM user ")
                .append(" where status="+Constant.ENABLE)
                .append(" and departmentId=?");

        return jdbcTemplate.queryForList(sql.toString(),departmentId);
    }


    private Map<String, Object> getCondition(HttpServletRequest request) throws Exception {
        Map<String, Object> condition = new HashMap<>();
        if (StrUtil.isNotBlank(request.getParameter("isProjectUser")) && StrUtil.isNotBlank(request.getParameter("projectID"))) {
            List<Integer> projectUsers = this.getProjectUserId(Integer.valueOf(request.getParameter("projectID")));
            if (projectUsers.size() > 0) {
                condition.put(" AND id NOT IN(" + BaseUtil.getPlaceholder(projectUsers.size()) + ")", projectUsers);
            }
        }
      /*  if (StrUtil.isNotBlank(request.getParameter("departmentIDS"))) {
            condition.put(" AND departmentId IN(" + BaseUtil.getPlaceholder(request.getParameter("departmentIDS")) + ")", BaseUtil.getArgs(request.getParameter("departmentIDS"), Integer.class));
        }*/
        if (StrUtil.isNotBlank(request.getParameter("roleIDS"))) {
            condition.put(" AND id IN(SELECT userId FROM user_role WHERE roleId IN(" + BaseUtil.getPlaceholder(request.getParameter("roleIDS")) + "))", BaseUtil.getArgs(request.getParameter("roleIDS"), Integer.class));
        }
        if (StrUtil.isNotBlank(request.getParameter("name"))) {
            condition.put(" AND name LIKE ?", "%" + request.getParameter("name") + "%");
        }
        if (StrUtil.isNotBlank(request.getParameter("userName"))) {
            condition.put(" AND userName LIKE ?", "%" + request.getParameter("userName") + "%");
        }
        return condition;
    }

    private List<Integer> getProjectUserId(Integer projectID) throws Exception {
        final String sql = "SELECT userID FROM projectuserrole WHERE projectID=?";
        return userDao.getJdbcTemplate().queryForList(sql, Integer.class, projectID);
    }

    public Result updatePassword(User user, String oldPassword, String newPassword) throws Exception {
        if (StrUtil.isNotBlank(newPassword)) {
            if (user.getPassword().equals(oldPassword)) {
                user.setPassword(newPassword);
                this.update(user);
                return new Result(Constant.SUCCESS, "修改成功");
            } else {
                return new Result(Constant.FAIL, "原密码错误");
            }
        } else {
            return new Result(-2, "用户密码不能为空");
        }
    }

    @Override
    public User getByCondition(Map<String, String> condition) throws Exception {
        return super.getByCondition(condition);
    }

    /**
     *根据用户和机构id返回用机构信息
     * @return
     * @throws Exception
     * 20180716
     */
    public List<Map<String, Object>> queryUsersOrgs (String userIds)throws Exception{

        StringBuffer sql=new StringBuffer("SELECT uo.userId AS userId,");
        sql.append("uo.orgId AS orgId,");
        sql.append("o.name AS orgName");
        sql.append(" FROM user_org uo LEFT JOIN department o ON o.id=uo.orgId WHERE 1=1 ");
        List<Object> args=null;
        if(BaseUtil.isNotEmpty(userIds)){
            sql.append(" AND uo.userId IN ");
            args=BaseUtil.toInConds(sql,userIds);
        }

        sql.append(" GROUP BY uo.userId,uo.orgId ");

        List<Map<String, Object>> usersOrgs=null;

        if(args!=null){
            usersOrgs=userDao.getJdbcTemplate().queryForList(sql.toString(),args.toArray());
        }
        return usersOrgs;
    }

    /**
     *根据用户和机构id返回用机构角色信息
     * @return
     * @throws Exception
     * 20180710
     */
    public List<Map<String, Object>> queryUsersOrgsRoles (String userIds,String orgIds)throws Exception{

        StringBuffer sql=new StringBuffer("SELECT uor.userId AS userId,");
        sql.append("uor.orgId AS orgId,");
        sql.append("o.name AS orgName,");
        sql.append("GROUP_CONCAT(r.id) AS roleIds,");
        sql.append("GROUP_CONCAT(r.name) AS roleNames ");
        sql.append(" FROM user_org_role uor LEFT JOIN department o ON o.id=uor.orgId LEFT JOIN role r ON uor.roleId=r.id  WHERE 1=1 ");
        List<Object> args=new ArrayList<Object>();

        if(BaseUtil.isNotEmpty(orgIds)){
            sql.append(" AND uor.orgId IN ");
            args.addAll(BaseUtil.toInConds(sql,orgIds));
        }
        if(BaseUtil.isNotEmpty(userIds)) {
            sql.append(" AND uor.userId IN ");
            args.addAll(BaseUtil.toInConds(sql, userIds));
        }

        sql.append(" GROUP BY uor.userId,uor.orgId ");

        List<Map<String, Object>> roles=null;

        if(args.size()>0){
            roles=userDao.getJdbcTemplate().queryForList(sql.toString(),args.toArray());
        }
        return roles;
    }

    /**
     *根据用户和机构id返回用机构角色信息，角色根据用户机构进行分组，多个角色以逗号','分隔。
     * @return
     * @throws Exception
     * 20180712
     */
    public List<Map<String, Object>> queryUsersOrgsRolesStr (String userIds,String orgIds)throws Exception{

        StringBuffer sql=new StringBuffer("SELECT uor.userId AS userId,");
        sql.append("uor.orgId AS orgId,");
        sql.append("GROUP_CONCAT(r.id) AS roleIds,");
        sql.append("GROUP_CONCAT(r.name) AS roleNames ");
        sql.append(" FROM user_org_role uor LEFT JOIN role r ON uor.roleId=r.id ");
        sql.append(" WHERE uor.orgId IN ");

        List<Object> args1=BaseUtil.toInConds(sql,orgIds);
        sql.append(" AND uor.userId IN ");
        List<Object> args2=BaseUtil.toInConds(sql,userIds);

        if(args2!=null){
            args1.addAll(args2);
        }

        sql.append(" GROUP BY uor.userId,uor.orgId ");

        List<Map<String, Object>> roles=null;

        if(args1!=null&&args2!=null){
            roles=userDao.getJdbcTemplate().queryForList(sql.toString(),args1.toArray());
        }
        return roles;
    }

    /**
     * 添加或更新用户机构角色
     * @author wonderful
     * @param userId
     * @param orgsRoles
     * @throws Exception
     * 20180711
     */
//    @CacheEvict(value="user_org_Cache",allEntries=true)
    public boolean insertOrUpdateUserOrgsRoles(Integer userId, List<Map> orgsRoles)throws Exception{

        boolean isSuccess=false;
        if (userId != null) {

            StringBuffer delUorSql=new StringBuffer("DELETE FROM user_org_role WHERE userId=? ");
            StringBuffer insertUorSql=new StringBuffer("INSERT INTO user_org_role (userId,orgId,roleId) ");
            StringBuffer delUoSql=new StringBuffer("DELETE FROM user_org WHERE userId=?");
            StringBuffer insertUo =new StringBuffer("INSERT INTO user_org (userId,orgId) ");
            insertUo.append("SELECT userId,orgId FROM user_org_role uor WHERE userId=? GROUP BY userId,orgId ");
            StringBuffer delUrSql=new StringBuffer("DELETE FROM user_role WHERE userId=?");
            StringBuffer insertUr =new StringBuffer("INSERT INTO user_role (userId,roleId) ");
            insertUr.append("SELECT userId,roleId FROM user_org_role uor WHERE userId=? AND roleId IS NOT NULL GROUP BY userId,roleId ");

            List<Object> args = new ArrayList<Object>();
            int i = 1;
            if(orgsRoles!=null){
                for (Map<String, Object> orgRole : orgsRoles) {
                    args.add(userId);
                    args.add(orgRole.get("orgId"));
                    args.add(orgRole.get("roleId"));
                    if (i == orgsRoles.size()) {
                        insertUorSql.append(" SELECT ?,?,? FROM DUAL ");
                    } else {
                        insertUorSql.append(" SELECT ?,?,? FROM DUAL UNION");
                    }
                    i++;
                }

            }

            Object[] argUserId=new Object []{userId};
            userDao.getJdbcTemplate().update(delUorSql.toString(),argUserId);
            if(args!=null&&args.size()>0)
                userDao.getJdbcTemplate().update(insertUorSql.toString(),args.toArray());
            userDao.getJdbcTemplate().update(delUoSql.toString(),argUserId);
            if(args!=null&&args.size()>0)
                userDao.getJdbcTemplate().update(insertUo.toString(),argUserId);
            userDao.getJdbcTemplate().update(delUrSql.toString(),argUserId);
            if(args!=null&&args.size()>0)
                userDao.getJdbcTemplate().update(insertUr.toString(),argUserId);

            isSuccess=true;

        }

        return isSuccess;
    }


    /**
     *根据用户id查询用户项目信息
     * @return
     * @throws Exception
     * 20180717
     */
    public List<Map<String, Object>> queryUsersProjects (String userIds)throws Exception{

        StringBuffer sql=new StringBuffer("SELECT up.userId AS userId,");
        sql.append("p.projectId AS projectId,");
        sql.append("p.projectName AS projectName ");
        sql.append(" FROM user_project up LEFT JOIN project p ON p.projectId=up.projectId WHERE 1=1 ");
        List<Object> args=null;
        if(BaseUtil.isNotEmpty(userIds)){
            sql.append(" AND up.userId IN ");
            args=BaseUtil.toInConds(sql,userIds);
        }

        sql.append(" GROUP BY up.userId,up.projectId ");

        List<Map<String, Object>> usersProjects=null;

        if(args!=null){
            usersProjects=userDao.getJdbcTemplate().queryForList(sql.toString(),args.toArray());
        }
        return usersProjects;
    }

    /**
     *根据用户或项目id返回用户项目标段信息
     * @return
     * @throws Exception
     * 20180716
     */
    public List<Map<String, Object>>  queryUsersProjectsSections (String userIds,String projectIds)throws Exception{

        StringBuffer sql=new StringBuffer("SELECT ups.userId AS userId,");
        sql.append("p.projectId AS projectId,");
        sql.append("p.projectName AS projectName,");
        sql.append("GROUP_CONCAT(s.sectionId) AS sectionIds,");
        sql.append("GROUP_CONCAT(s.sectionName) AS sectionNames ");
        sql.append(" FROM user_project_section ups LEFT JOIN section s ON ups.sectionId=s.sectionId ");
        sql.append(" LEFT JOIN project p ON ups.projectId=p.projectId WHERE 1=1 ");
        List<Object> args = new ArrayList<Object>();
        if(BaseUtil.isNotEmpty(projectIds)){
            sql.append(" AND ups.projectId IN ");
            args.addAll(BaseUtil.toInConds(sql, projectIds));
        }
        if(BaseUtil.isNotEmpty(userIds)) {
            sql.append(" AND ups.userId IN ");
            args.addAll(BaseUtil.toInConds(sql, userIds));
        }

        sql.append(" GROUP BY ups.userId,ups.projectId ");

        List<Map<String, Object>> usersProjectsSections=null;

        if(args.size()>0){
            usersProjectsSections=userDao.getJdbcTemplate().queryForList(sql.toString(),args.toArray());
        }
        return usersProjectsSections;
    }

    /**
     * 添加或更新用户项目标段
     * @author wonderful
     * @param userId
     * @param projectsSections
     * @throws Exception
     * 20180711
     */
    @CacheEvict(value="user_project_Cache",allEntries=true)
    public boolean insertOrUpdateUserProjectsSections(Integer userId, List<Map> projectsSections)throws Exception{

        boolean isSuccess=false;
        if (userId != null) {

            StringBuffer delUpsSql=new StringBuffer("DELETE FROM user_project_section WHERE userId=? ");
            StringBuffer insertUpsSql=new StringBuffer("INSERT INTO user_project_section (userId,projectId,sectionId) ");
            StringBuffer delUpSql=new StringBuffer("DELETE FROM user_project WHERE userId=?");
            StringBuffer insertUp =new StringBuffer("INSERT INTO user_project (userId,projectId) ");
            insertUp.append("SELECT userId,projectId FROM user_project_section ups WHERE userId=? GROUP BY userId,projectId ");

            List<Object> args = new ArrayList<Object>();
            if(projectsSections!=null&&projectsSections.size()>0) {
                int i = 1;
                for (Map<String, Object> projectSection : projectsSections) {
                    args.add(userId);
                    args.add(projectSection.get("projectId"));
                    args.add(projectSection.get("sectionId"));
                    if (i == projectsSections.size()) {
                        insertUpsSql.append(" SELECT ?,?,? FROM DUAL ");
                    } else {
                        insertUpsSql.append(" SELECT ?,?,? FROM DUAL UNION");
                    }
                    i++;
                }
            }
            Object[] argUserId=new Object []{userId};
            userDao.getJdbcTemplate().update(delUpsSql.toString(),argUserId);
            if(args!=null&&args.size()>0)
                userDao.getJdbcTemplate().update(insertUpsSql.toString(),args.toArray());
            userDao.getJdbcTemplate().update(delUpSql.toString(),argUserId);
            if(args!=null&&args.size()>0)
                userDao.getJdbcTemplate().update(insertUp.toString(),argUserId);

            isSuccess=true;

        }

        return isSuccess;
    }

    /**
     * 获取当前用户的组织IDS,以逗号风格
     * @author superKing_zhanglh
     * @param userID
     * @return
     */
    /*   @Cacheable(value="user_org_Cache", key="'get'+#UserOrgIDs")*/
    public String getUserOrgIDs(Integer userID){
        List<Map<String, Object>> list = userDao.getJdbcTemplate().queryForList("select uo.orgId from user_org uo where uo.userId = " + userID + "");
        StringBuilder ids = new StringBuilder();
        for (Map<String, Object> jdbcMapResult : list)
        {

            String orgId = jdbcMapResult.get("orgId")+"";
            ids.append(orgId).append(",");

        }
        return ids.substring(0, ids.length()>1?ids.length()-1:0);
    }

    /**
     * 获取当前用户的机构信息
     * @author superKing_zhanglh
     * @param userID
     * @return
     */
    /*  @Cacheable(value="user_org_Cache", key="#root.method.name")*/
    public List<UserVO>  getUserOrg(Integer userID){
        List<UserVO> userOrgs = new ArrayList<>();
        StringBuffer sql=new StringBuffer("select  \n" +
                "u.id as userId,\n" +
                "u.name as userName,\n" +
                "d.id as orgId,\n" +
                "d.name as orgName,\n" +
                "d.type as orgType "+
                "from user_org ur\n" +
                "left join user u on ur.userId = u.id\n" +
                "left join department d on d.id = ur.orgId\n" +
                "where ur.userId="+userID+"");
        List<Map<String, Object>> lists = userDao.getJdbcTemplate().queryForList(sql.toString());

        for (Map<String, Object> jdbcMapResult : lists)
        {
            //利用工具类反向组装成自定义VO对象
            userOrgs.add(BaseUtil.reflect(UserVO.class, jdbcMapResult));
        }
        return userOrgs;
    }


    /**
     * 获取当前用户的机构角色IDS,以逗号风格
     * @author superKing_zhanglh
     * @param userID
     * @return
     */
    /*    @Cacheable(value="user_org_Cache", key="'get'+#UserOrgRoleIDs")*/
    public String getUserOrgRoleIDs(Integer userID){
        List<Map<String, Object>> list = userDao.getJdbcTemplate().queryForList("select uor.roleId \n" +
                "from user_org_role uor \n" +
                "where uor.userId = " + userID + "");
        StringBuilder ids = new StringBuilder();
        for (Map<String, Object> jdbcMapResult : list)
        {

            String roleId = jdbcMapResult.get("roleId")+"";
            ids.append(roleId).append(",");

        }
        return ids.substring(0, ids.length()>1?ids.length()-1:0);
    }

    /**
     * 获取当前用户机构角色信息
     * @author superKing_zhanglh
     * @param userID
     * @return
     */
    /* @Cacheable(value="user_org_Cache", key="#root.method.name")*/
    public List<UserVO> getUserOrgRole(Integer userID){
        List<UserVO> userOrgRoles = new ArrayList<UserVO>();

        StringBuffer sql=new StringBuffer("select  \n" +
                "u.id as userId,\n" +
                "u.name as userName,\n" +
                "d.id as orgId,\n" +
                "d.name as orgName,\n" +
                "r.id as roleId,\n" +
                "r.name as roleName\n" +
                "from user_org_role uor\n" +
                "LEFT JOIN user u on uor.userId = u.id\n" +
                "LEFT JOIN department d on d.id = uor.orgId\n" +
                "LEFT JOIN role r on r.id = uor.roleId\n" +
                "where uor.userId="+userID+"");


        List<Map<String, Object>> lists = userDao.getJdbcTemplate().queryForList(sql.toString());

        for (Map<String, Object> jdbcMapResult : lists)
        {
            //利用工具类反向组装成自定义VO对象
            userOrgRoles.add(BaseUtil.reflect(UserVO.class, jdbcMapResult));
        }
        return userOrgRoles;
    }


    /**
     * 获取当前用户的项目IDS,以逗号风格
     * @author superKing_zhanglh
     * @param userID
     * @return
     */
    //@Cacheable(value="user_project_Cache", key="'get'+#UserProjectIDs")
    public String getUserProjectIDs(Integer userID){
        List<Map<String, Object>> list = userDao.getJdbcTemplate().queryForList("select up.projectId \n" +
                "from user_project up \n" +
                "where up.userId = " + userID + "");
        StringBuilder ids = new StringBuilder();
        for (Map<String, Object> jdbcMapResult : list)
        {

            String projectId = jdbcMapResult.get("projectId")+"";
            ids.append(projectId).append(",");

        }
        return ids.substring(0, ids.length()>1?ids.length()-1:0);
    }

    /**
     * 获取当前用户的项目标段IDS,以逗号风格
     * @author superKing_zhanglh
     * @change wonderful 20180907
     * @param userID
     * @return
     */
    //@Cacheable(value="user_project_Cache", key="'get'+#UserProjectSectionIDs")
    public String getUserProjectSectionIDs(Integer userID){
        List<Map<String, Object>> list = userDao.getJdbcTemplate().queryForList("SELECT ups.sectionId \n" +
                "FROM user_project_section ups \n" +
                "WHERE ups.userId = " + userID + " ");
        StringBuilder ids = new StringBuilder();
        for (Map<String, Object> jdbcMapResult : list)
        {

            String sectionId = jdbcMapResult.get("sectionId")+"";
            ids.append(sectionId).append(",");

        }
        return ids.substring(0, ids.length()>1?ids.length()-1:0);
    }

    /**
     * 获取当前用户的项目信息
     * @author superKing_zhanglh
     * @param userID
     * @return
     */
    //@Cacheable(value="user_project_Cache", key="#root.method.name")
    public List<ProjectVO> getUserProject(Integer userID){
        List<ProjectVO> userProjects = new ArrayList<ProjectVO>();

        StringBuffer sql = new StringBuffer("select  \n" +
                "u.id as userId,\n" +
                "u.name as userName,\n" +
                "p.projectId ,\n" +
                "p.projectNo,\n" +
                "p.projectName\n" +
                "from user_project up\n" +
                "LEFT JOIN user u on up.userId = u.id\n" +
                "LEFT JOIN project p on up.projectId = p.projectId\n" +
                "where up.userId="+userID+"");


        List<Map<String, Object>> lists = userDao.getJdbcTemplate().queryForList(sql.toString());

        for (Map<String, Object> jdbcMapResult : lists)
        {
            //利用工具类反向组装成自定义VO对象
            userProjects.add(BaseUtil.reflect(ProjectVO.class, jdbcMapResult));
        }
        return userProjects;
    }

    /**
     * 获取当前用户的项目标段信息
     * @author superKing_zhanglh
     * @param userID
     * @return
     */
    //@Cacheable(value="user_project_Cache", key="#root.method.name")
    public List<ProjectVO> getUserProjectSection(Integer userID){
        List<ProjectVO> userProjectSections = new ArrayList<ProjectVO>();

        StringBuffer sql=new StringBuffer("select  \n" +
                "u.id as userId,\n" +
                "u.name as userName,\n" +
                "p.projectId ,\n" +
                "p.projectNo,\n" +
                "p.projectName,\n" +
                "s.sectionId ,\n" +
                "s.sectionNo,\n" +
                "s.sectionName,\n" +
                "s.conOrgId,\n" +
                "d.name as conOrgName\n" +
                "from user_project_section ups\n" +
                "LEFT JOIN user u on ups.userId = u.id\n" +
                "LEFT JOIN project p on ups.projectId = p.projectId\n" +
                "LEFT JOIN section s on ups.sectionId = s.sectionId\n" +
                "LEFT JOIN department d on d.id = s.conOrgId\n" +
                "where ups.userId="+userID+" ORDER BY s.orderNo ");


        List<Map<String, Object>> lists = userDao.getJdbcTemplate().queryForList(sql.toString());

        for (Map<String, Object> jdbcMapResult : lists)
        {
            //利用工具类反向组装成自定义VO对象
            userProjectSections.add(BaseUtil.reflect(ProjectVO.class, jdbcMapResult));
        }
        return userProjectSections;
    }

    /**
     * 获取当前用户的角色IDS,以逗号风格
     * @author Pyj
     * @date 2018-08-20
     * @param userID
     * @return
     */
    public String getUserRoleIDs(Integer userID) {
        String sql = "select uo.roleId from user_role uo where uo.userId = " + userID + "";
        List<Map<String, Object>> list = userDao.getJdbcTemplate().queryForList(sql);
        StringBuilder roleIDs = new StringBuilder();
        for (Map<String, Object> jdbcMapResult : list) {
            String roleId = jdbcMapResult.get("roleId")+"";
            roleIDs.append(roleId).append(",");
        }
        return roleIDs.substring(0, roleIDs.length()>1?roleIDs.length()-1:0);
    }

    /**
     * 获取当前用户的角色名称,以逗号风格
     * @author Pyj
     * @date 2019-10-15
     * @param userID
     * @return
     */
    public String getUserRoleNames(Integer userID) {
        String sql = "select r.name AS roleName from user_role ur LEFT JOIN role r ON r.id=ur.roleId where ur.userId = " + userID + "";
        List<Map<String, Object>> list = userDao.getJdbcTemplate().queryForList(sql);
        StringBuilder roleNames = new StringBuilder();
        for (Map<String, Object> jdbcMapResult : list) {
            String roleName = jdbcMapResult.get("roleName")+"";
            roleNames.append(roleName).append(",");
        }
        return roleNames.substring(0, roleNames.length()>1?roleNames.length()-1:0);
    }

    /**
     * 获取当前用户的角色菜单IDS,以逗号风格
     * @author Pyj
     * @date 2019-11-11
     * @param roleIDS
     * @return
     */
    public String getUserRoleMenuIDs(String roleIDS) {
        String sql = "SELECT DISTINCT rm.menuId FROM role_menu rm WHERE rm.roleId IN ("+roleIDS+")";
        List<Map<String, Object>> list = userDao.getJdbcTemplate().queryForList(sql);
        StringBuilder menuIDs = new StringBuilder();
        for (Map<String, Object> jdbcMapResult : list) {
            String menuId = jdbcMapResult.get("menuId")+"";
            menuIDs.append(menuId).append(",");
        }
        return menuIDs.substring(0, menuIDs.length()>1?menuIDs.length()-1:0);
    }

    /**
     * 获取当前用户的角色菜单名称,以逗号风格
     * @author Pyj
     * @date 2019-11-12
     * @param menuIDs
     * @return
     */
    public String getUserRoleMenuNames(String menuIDs) {
        String sql = "SELECT m.name menuName FROM menu m WHERE m.id IN (" + menuIDs + ")";
        List<Map<String, Object>> list = userDao.getJdbcTemplate().queryForList(sql);
        StringBuilder roleMenuNames = new StringBuilder();
        for (Map<String, Object> jdbcMapResult : list) {
            String menuName = jdbcMapResult.get("menuName")+"";
            roleMenuNames.append(menuName).append(",");
        }
        return roleMenuNames.substring(0, roleMenuNames.length()>1?roleMenuNames.length()-1:0);
    }

    /**
     * 获取多个用户信息
     * @author Pyj
     * @date 2019-10-24
     * @param userIds
     * @return
     */
    public List<Map<String, Object>> getUsersByIds(String userIds) {
        String sql = "SELECT u.* FROM user u WHERE u.id IN ( " + userIds + " )";
        return userDao.getJdbcTemplate().queryForList(sql);
    }


    /**
     * 更新用户的clientId,确保clientId与userName对应唯一性
     * @author yuanp
     * @param usernName
     * @param cid
     * @return
     */
    public Result updateUserClientID(String usernName,String cid){
        //先清空所有所有相同的clientId
        StringBuffer sql=new StringBuffer("UPDATE user SET clientId='' where clientId = ? ");
        userDao.getJdbcTemplate().update(sql.toString(),cid);

        //更新当前用户的cid
        StringBuffer sqlCid = new StringBuffer("UPDATE user SET clientId = ? where userName = ? ");
        userDao.getJdbcTemplate().update(sqlCid.toString(),cid,usernName);

        return new Result(Constant.SUCCESS,"更新用户clientId成功！");
    }

    /**
     * 根据用户id 获取用户clientId
     * @param ids
     * @return
     * @throws Exception
     */
    public List<Map<String, Object>> getUserClientByids(String ids) throws Exception {
        StringBuffer sql = new StringBuffer();
        List<Object> args = new ArrayList<Object>();
        sql.append("select clientId from user where clientId is not null and id in ");
        args.addAll(BaseUtil.toInConds(sql,ids.substring(0,ids.length())));
        return userDao.getJdbcTemplate().queryForList(sql.toString(),args.toArray());
    }

    public String getClientId(Integer userId)throws Exception{
        User user = userDao.get(userId);
        return user.getClientId();
    }
}



